﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Press.MVC.Data.DataAccess.SqlServer;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Data;

namespace Press.MVC.Data
{
    public class DBUtil : IDBUtil
    {
        LinqBlogDataContext _db;

        public DBUtil()
        { }

        public DBUtil(LinqBlogDataContext dataContext)
        {
            _db = dataContext;
        }

        public void CreateDatabase()
        {
            using (LinqBlogDataContext db = new LinqBlogDataContext())
            {
                //TODO deal with errorMessage
                string errorMessage = "";
                if (!ValidateDatabaseConnection(out errorMessage))
                {
                    db.CreateDatabase();
                }
            }
        }

        public void DeleteDatabase()
        {
            using (LinqBlogDataContext db = new LinqBlogDataContext())
            {
                db.DeleteDatabase();
            }
        }
        public bool ValidateDatabaseConnection(out string errorMessage)
        {
            bool bDoesDBExist = false;
            try
            {
                using (LinqBlogDataContext db = new LinqBlogDataContext())
                {
                    if (db.DatabaseExists())
                    {
                        bDoesDBExist = true;
                    }
                }

                errorMessage = "";

            }
            catch (Exception e)
            {
                errorMessage = e.Message;
                bDoesDBExist = false;
            }

            return bDoesDBExist;
        }

        public bool AspnetMembershipSQlScriptInstall()
        {            
            string currentPath = System.IO.Path.GetFullPath(@".");            
            string fullPathAndFile = currentPath + Press.MVC.Data.Properties.Settings.Default.blog_MembershipInstallScriptFile;

            if (!ExecuteSqlFile(fullPathAndFile, Press.MVC.Data.Properties.Settings.Default.blog_mvcConnectionString))
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        // TODO For now Sample Data is generated via SQL(which was done for speed in the begining so we have test data), but in the future this should really be done using
        // the MVC Press existing Data/Service layer methods to add blogs, add posts, etc. 
        public bool SampleDataInstall()
        {
            string currentPath = System.IO.Path.GetFullPath(@".");
            string fullPathAndFile = currentPath + Press.MVC.Data.Properties.Settings.Default.blog_SampleData;

            if (!ExecuteSqlFile(fullPathAndFile, Press.MVC.Data.Properties.Settings.Default.blog_mvcConnectionString))
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        //TODO reuse LinqBlogDataContext above for the below method.
        private bool ExecuteSqlFile(string pathToScriptFile, string connectionString)
        {
            SqlConnection connection;

            try
            {
                StreamReader _reader = null;

                string sql = "";

                if (false == System.IO.File.Exists(pathToScriptFile))
                {

                    throw new Exception("The file " + pathToScriptFile + " does not exist.");
                }
                using (Stream stream = System.IO.File.OpenRead(pathToScriptFile))
                {
                    _reader = new StreamReader(stream);

                    connection = new SqlConnection(connectionString);

                    SqlCommand command = new SqlCommand();

                    connection.Open();
                    command.Connection = connection;
                    command.CommandType = System.Data.CommandType.Text;

                    while (null != (sql = ReadSQLStatementFromStream(_reader)))
                    {
                        command.CommandText = sql;

                        command.ExecuteNonQuery();
                    }
                    connection.Close();

                    _reader.Close();
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;

                //TODO we need to return the error..save for later
            }

            connection.Close();



        }

        /// <summary>
        /// ReadSQLStatementFromStream 
        /// </summary>
        /// <param name="_reader"></param>
        /// <returns></returns>
        private static string ReadSQLStatementFromStream(StreamReader _reader)
        {
            try
            {
                StringBuilder sb = new StringBuilder();

                string lineOfText;

                while (true)
                {
                    lineOfText = _reader.ReadLine();
                    if (lineOfText == null)
                    {

                        if (sb.Length > 0)
                        {
                            return sb.ToString();
                        }
                        else
                        {
                            return null;
                        }
                    }

                    if (lineOfText.TrimEnd().ToUpper() == "GO")
                    {
                        break;
                    }

                    sb.Append(lineOfText + Environment.NewLine);
                }

                return sb.ToString();
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public bool CreateAspNetApplication()
        {
            SqlConnection connection;

            try
            {
                connection = new SqlConnection(Press.MVC.Data.Properties.Settings.Default.blog_mvcConnectionString);
                SqlCommand command = new SqlCommand("aspnet_Applications_CreateApplication", connection);
                command.CommandType = CommandType.StoredProcedure;
                connection.Open();
                command.Connection = connection;
                command.Parameters.Add(new SqlParameter("@ApplicationId", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Output, false, 0, 50, "ApplicationId", DataRowVersion.Default, null));
                command.Parameters.Add(new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256, "ApplicationName"));
                command.Parameters["@ApplicationName"].Value = "mvcpress";
                command.UpdatedRowSource = UpdateRowSource.OutputParameters;
                command.ExecuteNonQuery();
                string applicationId = Convert.ToString(command.Parameters["@ApplicationId"].Value);
                connection.Close();

                return true;
            }
            catch (Exception ex)
            {
                return false;

                //TODO we need to return the error..save for later
            }
        }

        ///// <summary>
        ///// ValidateDatabaseConnection - Validate connection to database with supplied connnection string
        ///// </summary>
        ///// <param name="connectionString">connectionString for MS SQL Server Database</param>
        ///// <param name="errorMessage"></param>
        ///// <returns></returns>
        //public bool ValidateDatabaseConnection(string connectionString, out string errorMessage)
        //{
        //    try
        //    {
        //        SqlConnection connection = new SqlConnection(connectionString);
        //        connection.Open();
        //        connection.Close();

        //        errorMessage = "";

        //        return true;
        //    }
        //    catch (Exception e)
        //    {
        //        errorMessage = e.Message;
        //        return false;
        //    }
        //}

        public string GetConnectionString()
        {
            return Press.MVC.Data.Properties.Settings.Default.blog_mvcConnectionString;
        }
    }
}
